﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using NPOI.HSSF.UserModel;
using System.Web;

namespace CoolERP_Common
{
    public class ExcelHelper
    {
        /// <summary>
        /// 读取Excel表格数据,返回数据集DataSet
        /// </summary>
        /// <param name="strPath">所要打开的文件地址</param>
        /// <returns></returns>
        public static DataTable ImportFromExcel(System.IO.Stream stream)
        {
            ///建立连接,读取表格中的数据
            //string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
            ///
            //OleDbConnection myConn = new OleDbConnection(strCon);

            //myConn.Open();
            //DataTable dt = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            //string tableName = dt.Rows[0].ItemArray[2].ToString();

            //string strCom = string.Format("select * from [{0}]", tableName);///SQL操作语句,就是说:取得所有数据从Sheet1

            //OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            //DataSet myDataSet = new DataSet();///建立新的数据集myDataSet
            //myCommand.Fill(myDataSet, "table");///填充数据集
            //myConn.Close();

            //return myDataSet.Tables[0];

            HSSFWorkbook wordBook = new HSSFWorkbook(stream);
            HSSFSheet sheet = wordBook.GetSheetAt(0);
            DataTable dt = new DataTable();
            for (int i = 0; i < sheet.GetRow(0).Cells.Count; i++)
                dt.Columns.Add(new DataColumn(sheet.GetRow(0).Cells[i].ToString(), typeof(string)));

            for (int i = 0; i < sheet.PhysicalNumberOfRows; i++)
            {
                if (sheet.GetRow(i) != null)
                {
                    DataRow newrow = dt.NewRow();
                    for (int j = 0; j < sheet.GetRow(i).Cells.Count; j++)
                    {
                        newrow[j] = GetCellValue(sheet.GetRow(i).GetCell(j));
                    }
                    dt.Rows.Add(newrow);
                }
            }
            return dt;
        }

        private static string GetCellValue(HSSFCell cell)
        {
            if (cell != null)
            {
                try
                {
                    if (HSSFDateUtil.IsCellDateFormatted(cell))
                        return Convert.ToDateTime(cell.DateCellValue).ToString();
                    else
                        return cell.ToString();
                }
                catch
                {
                    return cell.ToString();
                }
            }
            else
                return "";
        }

        /// <summary>
        /// 读取DataSet导出Excel文件
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="strPath">所要保存的文件地址</param>
        /// <param name="ExportType">1：直接弹出保存窗口，2：保存在虚拟目录下</param>
        public static void ExportToExcel(DataTable dt, string strPath, int ExportType)
        {
            if (ExportType == 1)
            {
                StringWriter sw = new StringWriter();
                string str = "";

                //写标题
                foreach (System.Data.DataColumn col in dt.Columns)
                    str += string.Format("{0}\t", col);
                sw.WriteLine(str);

                //写内容
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string tempStr = "";
                    for (int j = 0; j < dt.Columns.Count; j++)
                        tempStr += string.Format("{0}\t", Convert.ToString(dt.Rows[i][j].ToString()));
                    sw.WriteLine(tempStr);
                }
                HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + strPath);
                HttpContext.Current.Response.ContentType = "application/ms-excel";
                HttpContext.Current.Response.Write(sw);
                HttpContext.Current.Response.End();
                sw.Close();
                sw.Dispose();
            }
            else
            {
                HSSFWorkbook wordBook = new HSSFWorkbook();
                HSSFSheet sheet = wordBook.CreateSheet("Sheet1");
                //写标题
                HSSFRow rowTitle = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    rowTitle.CreateCell(i).SetCellValue(dt.Columns[i].ToString());
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    HSSFRow row = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Rows[i].ItemArray.Length; j++)
                        row.CreateCell(j).SetCellValue(dt.Rows[i].ItemArray[j].ToString());
                }
                FileStream file = new FileStream(strPath, FileMode.Create);
                wordBook.Write(file);
                file.Close();
            }
        }
    }
}
